INDEX DEFINITION STATEMENTS¶

CREATE INDEX¶

Creates an index to a specified table by using the CREATE INDEX statement. Regarding writing index name, see Identifier.

For how to use indexes on the SELECT statement like Using SQL Hint, Descending Index, Covering Index, Index Skip Scan, ORDER BY Optimization and GROUP BY Optimization, and how to create Filtered Index and Function-based Index, see Updating Statistics.

CREATE [UNIQUE] INDEX index_name ON table_name <index_col_desc> ;

    <index_col_desc> ::=
        { ( column_name [ASC | DESC] [ {, column_name [ASC | DESC]} ...] ) [ WHERE <filter_predicate> ] |
        (function_name (argument_list) ) }
            [COMMENT 'index_comment_string']

• UNIQUE: creates an index with unique values.
• index_name: specifies the name of the index to be created. The index name must be unique in the table.
• table_name: specifies the name of the table where the index is to be created.
• column_name: specifies the name of the column where the index is to be applied. To create a composite index, specify two or more column names.
• ASC | DESC: specifies the sorting order of columns.
• <filter_predicate>: defines the conditions to create filtered indexes. When there are several comparison conditions between a column and a constant, filtering is available only when the conditions are connected by using AND. Refer to Filtered Index for more details.
• function_name (argument_list): Defines the conditions to create function-based indexes. Regarding this, definitely watch Function-based Index.
• index_comment_string: specifies a comment of an index.

The following example shows how to create a descending index.

CREATE INDEX gold_index ON participant(gold DESC);

The following example shows how to create a multiple column index.

CREATE INDEX name_nation_idx ON athlete(name, nation_code) COMMENT 'index comment';

CREATE TABLE tbl (a int default 0, b int, c int);

CREATE INDEX i_tbl_b on tbl (b) COMMENT 'index comment for i_tbl_b';

CREATE TABLE tbl2 (a INT, index i_tbl_a (a) COMMENT 'index comment', b INT);

ALTER TABLE tbl2 ADD INDEX i_tbl2_b (b) COMMENT 'index comment b';

SHOW CREATE TABLE table_name;
SELECT index_name, class_name, comment from db_index where class_name ='classname';
SHOW INDEX FROM table_name;

$ csql -u dba demodb

csql> ;sc tbl

ALTER INDEX¶

The ALTER INDEX statement rebuilds an index or adds/changes the comment of an index. Rebuilding an index is a job which drops and recreates an index.

The following is a syntax of rebuilding an index.

ALTER INDEX index_name ON table_name REBUILD;

• index_name: specifies the name of the index to be recreated. The index name must be unique in the table.
• table_name: specifies the name of the table where the index is recreated.
• REBUILD: recreate an index with the same structure as the one already created.
• index_comment_string: specifies a comment of an index.

The following is an example of recreating index.

CREATE INDEX i_game_medal ON game(medal);
ALTER INDEX i_game_medal ON game COMMENT 'rebuild index comment' REBUILD ;

If you want to add or change a comment of the index without rebuilding an index, add a COMMENT clause and remove REBUILD keyword as follows:

ALTER INDEX index_name ON table_name COMMENT 'index_comment_string' ;

The below is a syntax to only add or change a comment without rebuilding an index.

ALTER INDEX i_game_medal ON game COMMENT 'change index comment' ;

The following is a syntax of renaming an index.

ALTER INDEX old_index_name ON table_name RENAME TO new_index_name [COMMENT 'index_comment_string'] ;

DROP INDEX¶

Use the DROP INDEX statement to drop an index. An index also can be dropped with DROP CONSTRAINT clause.

DROP INDEX index_name ON table_name ;

• index_name: specifies the name of the index to be dropped.
• table_name: specifies the name of the table whose index is dropped.

The following is an example of dropping an index:

DROP INDEX i_game_medal ON game;